package com.mipo.dao.imp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.mipo.dao.BookDao;
import com.mipo.util.DBUtils;
import com.mipo.util.PageTools;
import com.mipo.vo.Book;
import com.mipo.vo.BookReview;
import com.mipo.vo.OrderItem;
import com.mipo.vo.Orders;

public class BookDaoImp implements BookDao {
	public boolean insertOrder(Orders o) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("insert into orders values(?,?,?,?,?,?,?,?,?)");
		Connection conn = DBUtils.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql.toString());
		ps.setString(1, o.getOrderId());
		ps.setInt(2, o.getAccountId());
		ps.setString(3, o.getConsigneeName());
		ps.setString(4, o.getConsigneePhon());
		ps.setString(5, o.getConsigneeAddress());
		ps.setString(6, o.getPost());
		ps.setString(7, o.getBale());
		ps.setString(8, o.getPay());
		ps.setInt(9, o.getStates());
		int r = ps.executeUpdate();
		DBUtils.close(conn);
		return r > 0 ? true : false;
	}

	public boolean insertOrderItem(OrderItem oi) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("insert into orderItem values(?,?,?,?)");
		Connection conn = DBUtils.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql.toString());
		ps.setString(1, oi.getOrderId());
		ps.setInt(2, oi.getBookId());
		ps.setInt(3, oi.getCounts());
		ps.setFloat(4, oi.getPrice());
		int r = ps.executeUpdate();
		DBUtils.close(conn);
		return r > 0 ? true : false;
	}

	public List<Book> list(PageTools t, String type) throws Exception {
		List<Book> list = new ArrayList<Book>();
		StringBuffer sql = new StringBuffer();
		sql.append(" select top " + t.getEveryRows()
				+ " * from  book b ,booktype bt  ");
		sql.append(" where b.typeid=bt.typeid  ");
		if (type != null && type.length() > 0) {
			sql.append(" and bt.typeId = ? ");
		}
		sql.append(" and bookid not in ( ");
		sql.append(" select top " + (t.getCurrentPage() - 1) * t.getEveryRows()
				+ " bookid from  book b ,booktype bt  ");
		sql.append(" where b.typeid=bt.typeid ");
		if (type != null && type.length() > 0) {
			sql.append(" and bt.typeId = ? ");
		}
		sql.append(" order by b.bookid desc)  order by b.bookid desc");
		Connection conn = DBUtils.getConnection();
		PreparedStatement pst = conn.prepareStatement(sql.toString());
		if (type != null && type.length() > 0) {
			pst.setInt(1, Integer.valueOf(type));
			pst.setInt(2, Integer.valueOf(type));
		}
		ResultSet rs = pst.executeQuery();
		while (rs.next()) {
			Book book = new Book(rs.getInt(1), rs.getInt(2), rs.getString(3),
					rs.getString(4), rs.getString(5), rs.getString(6), rs
							.getString(7), rs.getString(8), 1, rs.getFloat(9),
					rs.getFloat(10), rs.getString(12));
			// book.setBooktypeName(rs.getString(14));
			list.add(book);
		}
		DBUtils.close(conn);
		return list;
	}

	public List<Book> searchList(String bookName) throws Exception {
		List<Book> list = new ArrayList<Book>();
		StringBuffer sql = new StringBuffer();
		String com=new String(bookName.getBytes("iso-8859-1"),"gbk");
		sql.append(" select * from  book b ,booktype bt  ");
		sql.append(" where b.typeid=bt.typeid ");
		sql.append(" and b.bookName like '%" + com + "%' ");
		Connection conn = DBUtils.getConnection();
		PreparedStatement pst = conn.prepareStatement(sql.toString());
		ResultSet rs = pst.executeQuery();
		while (rs.next()) {
			Book book = new Book(rs.getInt(1), rs.getInt(2), rs.getString(3),
					rs.getString(4), rs.getString(5), rs.getString(6), rs
							.getString(7), rs.getString(8), 1, rs.getFloat(9),
					rs.getFloat(10), rs.getString(12));
			// book.setBooktypeName(rs.getString(14));
			list.add(book);
		}
		System.out
				.println(" select * from  book b ,booktype bt  where b.typeid=bt.typeid  and b.bookName like '%"
						+ com + "%' ");
		DBUtils.close(conn);
		return list;
	}

	public Book queryBook(int id) throws Exception {
		Book book = null;
		StringBuffer sql = new StringBuffer();
		sql
				.append("select  * from book b,booktype bt where b.typeId = bt.typeId");
		sql.append(" and b.bookId = ?");
		Connection conn = DBUtils.getConnection();
		PreparedStatement pst = conn.prepareStatement(sql.toString());
		pst.setInt(1, id);
		ResultSet rs = pst.executeQuery();
		if (rs.next()) {
			book = new Book(rs.getInt(1), rs.getInt(2), rs.getString(3), rs
					.getString(4), rs.getString(5), rs.getString(6), rs
					.getString(7), rs.getString(8), 1, rs.getFloat(9), rs
					.getFloat(10), rs.getString(12));
		}
		DBUtils.close(conn);
		return book;
	}

	public List<BookReview> querybr(int bookId, int pageNum, int everyPageRows)
			throws Exception {
		List<BookReview> list = new ArrayList<BookReview>();
		StringBuffer sql = new StringBuffer();
		sql.append("Select * FROM ( ");
		sql
				.append(" select ROW_NUMBER()Over(order by bv.sendtime desc) as rowId,* ");
		sql.append(" from bookreview bv where bv.bookid = ? ) as mytable ");
		sql.append("where rowId between ? and ?");
		System.out.println(sql);
		Connection conn = DBUtils.getConnection();
		PreparedStatement pst = conn.prepareStatement(sql.toString());
		pst.setInt(1, bookId);
		if (pageNum == 1) {
			pst.setInt(2, pageNum);
			pst.setInt(3, (pageNum) * everyPageRows);
		} else {
			pst.setInt(2, (pageNum - 1) * everyPageRows + 1);
			pst.setInt(3, (pageNum) * everyPageRows);
		}

		System.out.println((pageNum - 1) * everyPageRows + "------" + pageNum
				+ "------" + (pageNum) * everyPageRows);
		ResultSet rs = pst.executeQuery();
		while (rs.next()) {
			BookReview br = new BookReview(rs.getInt(2), rs.getInt(3), rs
					.getString(5), rs.getString(6));
			list.add(br);
		}
		DBUtils.close(conn);
		return list;
	}

	public boolean insertBR(BookReview br) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql
				.append("insert into bookreview(bookid,accountid,sendtime,[content]) values(?,?,getdate(),?)");
		Connection conn = DBUtils.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql.toString());
		ps.setInt(1, br.getBookid());
		ps.setInt(2, br.getReviewId());
		ps.setString(3, br.getContent());
		int r = ps.executeUpdate();
		DBUtils.close(conn);
		System.out
				.println("insert_sql:  "
						+ "insert into bookreview(bookid,accountid,sendtime,[content]) values("
						+ br.getBookid() + "," + br.getReviewId() + ",'"
						+ new Date() + "','" + br.getContent() + "')");
		return r > 0 ? true : false;
	}

	public boolean queryIsCanBR(int accid, int bookid) throws Exception {
		boolean b = false;
		String sql = "select * from orders o,orderitem oi where oi.orderid=o.orderid and bookid=? and accountid=?";
		Connection conn = DBUtils.getConnection();
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setInt(1, bookid);
		pst.setInt(2, accid);
		ResultSet rs = pst.executeQuery();
		if (rs.next()) {
			b = true;
		}
		System.out.println(accid + "?" + bookid);
		DBUtils.close(conn);
		return b;
	}

	public int getSumBR(int bookid) throws Exception {
		Connection conn = DBUtils.getConnection();
		String sql = "select  count(reviewId) from bookreview bv where 1=1 and bv.bookid = ? ";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, bookid);
		ResultSet rs = ps.executeQuery();
		int count = 0;
		while (rs.next()) {
			count = rs.getInt(1);
		}
		DBUtils.close(conn);
		return count;
	}

	public List<Book> queryBookListPage(int currentPage, int everyPageRows,
			Book book) throws Exception {
		List<Book> list = new ArrayList<Book>();
		StringBuffer sql = new StringBuffer();
		sql.append(" select top " + everyPageRows
				+ " * from  book b ,booktype bt ");
		sql.append(" where b.typeid=bt.typeid");
		sql.append(" and bookid not in");
		sql.append(" (");
		sql.append(" select top " + (currentPage - 1) * everyPageRows
				+ " bookid from  book b ,booktype bt ");
		sql.append(" where b.typeid=bt.typeid");
		sql.append(" )");

		System.out.print(" select top " + everyPageRows
				+ " * from  book b ,booktype bt ");
		System.out.print(" where b.typeid=bt.typeid");
		System.out.print(" and bookid not in");
		System.out.print(" (");
		System.out.print(" select top " + (currentPage - 1) * everyPageRows
				+ " bookid from  book b ,booktype bt ");
		System.out.print(" where b.typeid=bt.typeid");
		System.out.print(" )");
		Connection conn = DBUtils.getConnection();
		PreparedStatement pst = conn.prepareStatement(sql.toString());
		ResultSet rs = pst.executeQuery();
		while (rs.next()) {
			Book bo = new Book(rs.getInt(1), rs.getInt(2), rs.getString(3), rs
					.getString(4), rs.getString(5), rs.getString(6), rs
					.getString(7), rs.getString(8), 1, rs.getFloat(9), rs
					.getFloat(10), rs.getString(12));
			list.add(bo);
		}
		DBUtils.close(conn);

		return list;
	}

	public int queryBookRows(PageTools t, String type) throws Exception {
		Connection conn = DBUtils.getConnection();
		String sql = "select count(bookId) from book b where 1=1 ";
		if (type != null && type.length() > 0) {
			sql += "and b.typeId = ? ";
		}
		PreparedStatement ps = conn.prepareStatement(sql);
		if (type != null && type.length() > 0) {
			ps.setInt(1, Integer.valueOf(type));
		}
		ResultSet rs = ps.executeQuery();
		int count = 0;
		while (rs.next()) {
			count = rs.getInt(1);
		}
		DBUtils.close(conn);
		return count;
	}

	public boolean insertbook(Book b) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("insert into book(bookname,author,bookconcern,simplecontext"
				+ ",picture,marketvalue,price,typeid)"
				+ " values(?,?,?,?,?,?,?,?)");
		Connection conn = DBUtils.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql.toString());
		ps.setString(1, b.getBookName());
		ps.setString(2, b.getAuthor());
		ps.setString(3, b.getBookConcern());
		ps.setString(4, b.getSimpleContext());
		ps.setString(5, b.getPicture());
		ps.setFloat(6, b.getMarketValue());
		ps.setFloat(7, b.getPrice());
		ps.setInt(8, Integer.valueOf(b.getBooktypeName()));
		int r = ps.executeUpdate();
		DBUtils.close(conn);
		return r > 0 ? true : false;
	}

	public boolean edit(Book b) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql
				.append("update book set bookname=?,author=?,bookconcern=?,simplecontext=?,picture=?,marketvalue=?,price=?,typeid=? where bookid=?");
		Connection conn = DBUtils.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql.toString());
		ps.setString(1, b.getBookName());
		ps.setString(2, b.getAuthor());
		ps.setString(3, b.getBookConcern());
		ps.setString(4, b.getSimpleContext());
		ps.setString(5, b.getPicture());
		ps.setFloat(6, b.getMarketValue());
		ps.setFloat(7, b.getPrice());
		ps.setInt(8, Integer.valueOf(b.getBooktypeName()));
		ps.setInt(9, b.getBookId());
		int r = ps.executeUpdate();
		DBUtils.close(conn);
		return r > 0 ? true : false;
	}

	public boolean editOrderStatus(int status, String orderId) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("update orders set states = ? where orderId = ? ");
		Connection conn = DBUtils.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql.toString());
		ps.setInt(1, status);
		ps.setString(2, orderId);
		int r = ps.executeUpdate();
		DBUtils.close(conn);
		return r > 0 ? true : false;
	}

	public static void main(String[] args) throws Exception {
		new BookDaoImp().querybr(1, 1, 1);
	}
}
